<?php
if (file_exists(APPPATH.'models/basemodel.php')) include_once(APPPATH.'models/basemodel.php');

class CostCenterModel extends BaseModel {

	public function CostCenterModel() {
		parent::BaseModel();
		$this->setModel('job_costcenter');
		$this->obj =& get_instance();
		$this->load->database();
	}
	
	public function addDepartment($data) {
		$sql = "INSERT INTO costcenter
				SET centertitle = ".$this->db->escape($data->centertitle).",
					hourlyrate = ".intval($data->hourlyrate);
		
		$this->db->query($sql);
	
		return $this->db->insert_id();
	}
	
	public function getDepartment($depid) {
		$sql = "SELECT centertitle, hourlyrate
				FROM costcenter
				WHERE id = ".intval($depid)."
				LIMIT 1";
		
		$rs = $this->db->query($sql)->result();
	
		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function jobHasDepartment($jobid, $departmentid) {
		$sql = "SELECT JC.id
				FROM job_costcenter JC
				WHERE JC.jobid = ".intval($jobid)." 
					AND JC.deleted = 0
					AND JC.costcenterid = ".intval($departmentid)."
				LIMIT 1";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs[0]->id;
		else return false;
	}
	
	public function getCostCentersForJob($jobid) {
		$sql = "SELECT C.centertitle, JC.* 
				FROM costcenter C INNER JOIN job_costcenter JC ON (C.id = JC.costcenterid)
				WHERE JC.jobid = ".intval($jobid)." 
					AND JC.deleted = 0
				ORDER BY C.centertitle";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getUnusedCostCentersForJob($jobid) {
		$sql = "SELECT C.* 
				FROM costcenter C LEFT JOIN job_costcenter JC ON (C.id = JC.costcenterid AND JC.jobid = ".intval($jobid)." AND JC.deleted = 0)
				WHERE JC.id IS NULL
				ORDER BY C.centertitle";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function retrieve_by_pkey($idField) {
		$rs = $this->db->query("SELECT C.centertitle, JC.* FROM costcenter C INNER JOIN job_costcenter JC ON (C.id = JC.costcenterid) WHERE JC.id = '$idField' LIMIT 1")->result();

		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function getActivitiesForCostCenter($ccid) {
		$sql = "SELECT A.* 
				FROM activity A
				WHERE A.job_centerid = ".intval($ccid)."
					AND A.deleted = 0
				ORDER BY A.startdate, A.enddate";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
		
	}
	
	public function getActivitiesForJob($jobid, $noDep = false) {
		$sql = "SELECT A.*, C.centertitle
				FROM activity A LEFT JOIN job_costcenter J ON (A.job_centerid = J.id)
					LEFT JOIN costcenter C ON (J.costcenterid = C.id)
				WHERE A.deleted = 0
					AND ( J.jobid = ".intval($jobid)." OR A.jobid = ".intval($jobid).") ";
		
		if ($noDep) $sql .= " AND C.id IS NULL ";
		
		$sql .= " ORDER BY C.centertitle, A.startdate, A.enddate";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
		
	}
	
	public function getAllCenters() {
		$sql = "SELECT C.*
				FROM costcenter C 
				ORDER BY C.centertitle";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getAutoAssign($companyid, $jobid, $job_costcenterid, $costcenterid) {
		$sql = "SELECT U.id
				FROM users U
				WHERE U.costcenterid = ".intval($costcenterid)."
					AND autoassign = 1";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function autoAssign($companyid, $jobid, $job_costcenterid, $costcenterid) {
		$sql = "INSERT INTO assignment (userid, jobid, job_costcenterid)
				SELECT U.id, ".$jobid." as jobid, ".$job_costcenterid." as job_costcenterid
				FROM users U
				WHERE U.costcenterid = ".intval($costcenterid)."
					AND autoassign = 1";
		
		$this->db->query($sql);
		
		return true;
	}
}

?>